package me.zhengjie.utils;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;
import java.util.List;

public class ExcelUtils {

    public static String filePath = "E:\\aa.xlsx";

    public static void main (String args[]) {
        splitSheet2XLS();
    }

    /**
     *  void 读取wps版本word每一个表格的数据
     */
    public static void readTable() {
        try{
            FileInputStream in = new FileInputStream(filePath);//载入文档 //如果是office2007  docx格式
            if(filePath.toLowerCase().endsWith("docx")){
                //word 2007 图片不会被读取， 表格中的数据会被放在字符串的最后
                XWPFDocument xwpf = new XWPFDocument(in);//得到word文档的信息
//             List<XWPFParagraph> listParagraphs = xwpf.getParagraphs();//得到段落信息
                Iterator<XWPFTable> it = xwpf.getTablesIterator();//得到word中的表格

                while(it.hasNext()){

                    XWPFTable table = it.next();
                    List<XWPFTableRow> rows=table.getRows();
                    //读取每一行数据
                    for (int i = 1; i < rows.size(); i++) {
                        XWPFTableRow  row = rows.get(i);
                        //读取每一列数据
                        List<XWPFTableCell> cells = row.getTableCells();
                        String str="";
                        for (int j = 0; j < cells.size(); j++) {
                            XWPFTableCell cell=cells.get(j);
                            //输出当前的单元格的数据
                            str+=cell.getText();
                        }
                        System.out.println(str);
                    }
                }
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 拆分xls每一个sheet为一个工作表
     */
    public static void splitSheet2XLS() {
        try {
            // 第一步，创建一个webbook，对应一个Excel文件
            XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(filePath)));
            int total = workbook.getNumberOfSheets();//3
            workbook.close();
            System.out.println(total);
            for (int i = 0; i < total; i++) {// 获取每个Sheet表
                String tmpFilePath = "E:\\"+i+".xlsx";
                FileUtil.copyFile(filePath, tmpFilePath);
                File tmpFile = new File(tmpFilePath);
                XSSFWorkbook tmpWorkbook = new XSSFWorkbook(new FileInputStream(tmpFile));
                int total2 = tmpWorkbook.getNumberOfSheets();
                for (int j = total2-1; j >= 0 ; j--) {
                    if (i == j) {
                        continue;
                    }
                    tmpWorkbook.removeSheetAt(j);
                }
                String destPath = "E:\\"+workbook.getSheetName(i)+".xlsx";
                FileOutputStream destFileOut = new FileOutputStream(destPath);
                tmpWorkbook.write(destFileOut);
                tmpWorkbook.close();
                destFileOut.close();
                tmpFile.delete();//删除文件
            }
            System.out.println("ok");
        } catch(Exception e) {
            e.printStackTrace();
        }
    }

}
